import pandas as pd 

file = "../transcribed/mi_raw_antrim.xlsx"

df = pd.read_excel(file, header=0)

df['state'] = "MICHIGAN"
df["county"] = "ANTRIM"
df['office'] = "US PRESIDENT"
df['date'] = "2020-11-03"

df.loc[df.candidate == "Donald J. Trump", "party"] = "REPUBLICAN"
df.loc[df.candidate == "Joseph R. Biden", "party"] = "DEMOCRAT"
df.loc[df.candidate == "Jo Jorgensen", "party"] = "LIBERTARIAN"
df.loc[df.candidate == "Howie Hawkins", "party"] = "GREEN"
df.loc[df.candidate == "Don Blankenship", "party"] = "US TAXPAYERS PARTY"
df.loc[df.candidate == "Rocky De La Fuente", "party"] = "NATURAL LAW PARTY"

df['candidate'] = df['candidate'].str.upper()
df['jurisdiction'] = df['jurisdiction'].str.upper()

df = df[['state', 'county', 'date', 'office', 'jurisdiction', 'candidate', 'party',
         'original', 'audited', 'difference']]

df.sort_values(["jurisdiction"], inplace=True)

df.to_csv("../ready/mi_antrim.csv")
